package com.mjlf.spring_data_access.dao;

import com.mjlf.spring_data_access.entity.School;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.*;
import java.util.List;

/**
 * @auther a123
 * @create 2018-08-22 09:43
 * @desc 使用spring jdbc对数据库进行操作
 */
@Repository
public class SpringJDBCSchoolDao {

    private final String ADD_SQL = "INSERT INTO school_tbl(name) VALUES(?)";
    private final String QUERY_SQL_BYID = "SELECT id, name FROM school_tbl WHERE id = ?";

    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    /**
     * 通过spring添加数据到数据库， 使用jdbcTemplate模板，这个模板可以使用4中不同的方法进行数据添加
     *
     * @param school
     *
     * @return
     */

    @Transactional
    public int add(final School school) throws Exception {
        /**
         * 方式1， 通过这种方式获取不到新添加的主键id
         */
//        return jdbcTemplate.update(ADD_SQL, school.getName());

        /**
         * 方式2，使用PreparedStatementSetters进行参赛绑定，同时这种方式也是无法获取新添加主键的
         */
//        return jdbcTemplate.update(ADD_SQL, new PreparedStatementSetter() {
//            public void setValues(PreparedStatement preparedStatement) throws SQLException {
//                preparedStatement.setString(1, school.getName());
//            }
//        });

        /**
         * 方式3，使用这种方式可以获取到主键id值
         */
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int res = jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(ADD_SQL, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, school.getName());
                return ps;
            }
        }, keyHolder);
        school.setId(keyHolder.getKey().intValue());
        if (school.getName() == "123") {
            throw new Exception();
        }
        return res;
    }

    /**
     * 通过名称匹配sql参数
     * @param school
     * @return
     */
    public int addByParamsSource(School school){
        final String SQL = "INSERT INTO school_tbl(name) VALUES(:name)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int res =  namedParameterJdbcTemplate.update(SQL, new BeanPropertySqlParameterSource(school), keyHolder);
        school.setId(keyHolder.getKey().intValue());
        return res;
    }

    /**
     * 批量执行sql
     * @param schools
     * @return
     */
    public int[] batchAdd(final List<School> schools){
        return jdbcTemplate.batchUpdate(ADD_SQL, new BatchPreparedStatementSetter() {
            public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                preparedStatement.setString(1, schools.get(i).getName());
            }

            public int getBatchSize() {
                return schools.size();
            }
        });
    }

    public School querySchool(final int schoolId){
        final School school = new School();
        jdbcTemplate.query(QUERY_SQL_BYID, new PreparedStatementSetter() {
            public void setValues(PreparedStatement preparedStatement) throws SQLException {
                preparedStatement.setInt(1, schoolId);
            }
        }, new RowCallbackHandler() {
            public void processRow(ResultSet resultSet) throws SQLException {
                school.setId(schoolId);
                school.setName(resultSet.getString("name"));
            }
        });
        return school;
    }

    public List<School> queryMutilSchool(){
        final String QUERY_SQL = "SELECT id, name FROM school_tbl";
        final List<School> schools;

        /**
         * 流式处理， 使用RowCallbackHandler处理结果集是在获取数据的同时处理数据
         */
//        jdbcTemplate.query(QUERY_SQL, new RowCallbackHandler() {
//            public void processRow(ResultSet resultSet) throws SQLException {
//                School school = new School();
//                school.setId(resultSet.getInt("id"));
//                school.setName(resultSet.getString("name"));
//                schools.add(school);
//            }
//        });
        /**
         * 批量处理,使用这种方式是，首先将所有数据加载内存中， 然后在遍历处理，这种方式容易导致内存泄露
         */
        schools = jdbcTemplate.query(QUERY_SQL, new RowMapper<School>() {
            public School mapRow(ResultSet resultSet, int i) throws SQLException {
                School school = new School();
                school.setId(resultSet.getInt("id"));
                school.setName(resultSet.getString("name"));
                return school;
            }
        });
        return schools;
    }

    /**
     * 单值查询
     * @return
     */
    public int querySigniture(){
        final String QUERY_COUNT = "SELECT COUNT(*) FROM school_tbl";
        int count = jdbcTemplate.queryForObject(QUERY_COUNT, Integer.class);
        return count;
    }
}
